[AWS Black Belt Online Seminar] Amazon Redshift テーブル設計詳細ガイド：レポート

こんにちは、菊池です。

2017年8月29日（火）のAWS Black Belt Online Seminarを受講しましたので、レポートします。

今回は Amazon Redshift テーブル設計詳細ガイド ということで、。講師はシバタツの愛称でおなじみ、アマゾンウェブサービスジャパン、ソリューションアーキテクトの柴田竜典さんでした。

レポート

• Redshiftをお使いの方の悩み
  • クエリー性能を向上させたい <- 今回のテーマ
  • 同時実行をうまくさばきたい
  • 料金を抑えたい
• クエリ性能向上に大切なこと
  • 公式ドキュメントに記載
  • 最良のソートキーの選択
  • 最適な分散スタイルの選択

• 分散スタイルとソートキーの悩み
  • それぞれの方式のメリット/デメリット
  • その場その場で決めるので統一感がない
  • DDL設計者によって異なるポリシー

Agenda

• 分散スタイル
  • 分散スタイルはなぜ重要なのか
  • 分散キーの候補となる列の抽出
  • 分散スタイルの決定
  • 最適な分散キーの決定
• ソートキー
  • ソートキーについて
  • ソート形式の決定
  • 最良のソートキー列の決定

分散スタイルはなぜ重要なのか

• 分散スタイルとは何か
  • 1万枚の注文書と5人の名前が書かれたお得意様リストがある。
  • お得意様からの注文書を10人で抽出したい。
    • EVEN分散：注文書を上から1000枚ずつ10人に配る
    • KEY分散：注文書を、注文者名が「ア行で始まる人」「カ行で始まる人」…の10グループに分ける
    • ALL分散：名簿を10部コピーして10人に配る
  • 最適な分散スタイルの例：結合の観点から
    • ○：EVEN分散された注文書とALL分散された名簿
      • 10人が等しい分量の作業を行う
    • △：KEY分散された注文書とALL分散された名簿
      • ア行やカ行に比べ、ワ行の件数が少ない
    • ×：ALL分散された注文書とALL分散された名簿
      • 10人が全く同じ作業を行う
      • 注文書のコピーが無駄（Redshiftのストレージに相当）
  • 現実はさらに難しい
    • 様々なクエリーに対応する必要がある
    • 分散キーは1テーブルに1個しか選べない
    • フローチャートで機械的に判断しよう

分散キーの候補となる列の抽出

• その列のデータは均一に分散しているか
  • ア行vsワ行のように偏りがないか
  • NULLの割合が大きくないか

SELECT
col1,
COUNT(*)
FROM lineitems
GROUP BY col1
ORDER BY 2 DESC;

• その列のカーディナリティは高いか
  • カーディナリティはスライス数に対して相対的に大きい必要がある（スライスの4〜5倍が目安）

SELECT APPROXIMATE COUNT (DISTINCT sku)
FROM lineitems;

• その列でフィルターされるか
  • その列でフィルターされた結果、1つのスライスしか使われない場合も

SELECT
ti."table", ti.diststyle, RTRIM(a.attname) column_name,
COUNT(DISTINCT s.query ||'-'|| s.segment ||'-'|| s.step) as num_scans,
COUNT(DISTINCT CASE
WHEN TRANSLATE(TRANSLATE(info,')',' '),'(',' ') LIKE ('%'|| a.attname ||'%')
THEN s.query ||'-'|| s.segment ||'-'|| s.step END) AS column_filters
FROM stl_explain p
JOIN stl_plan_info i ON (i.userid = p.userid AND i.query = p.query AND i.nodeid=p.nodeid )
JOIN stl_scan s ON (s.userid = i.userid AND s.query = i.query AND s.segment = i.segment AND s.step = i.step)
JOIN svv_table_info ti ON ti.table_id = s.tbl
JOIN pg_attribute a ON (a.attrelid = s.tbl AND a.attnum > 0)
WHERE s.tbl IN ('<table_name>'::regclass::oid)
GROUP BY 1, 2, 3, a.attnum
ORDER BY attnum;

• その列は第1ソートキーか
  • フィルターされる列はソートキーにも使用されていることが少なくない
  • ソートキーにも使用されている場合、ゾーンマップが効く可能性
  • ゾーンマップが効くとスキャン速度の大幅向上が、スライス数の少なさを補うことも
    • IO量が劇的に減る

SELECT attname
FROM pg_attribute
WHERE attrelid = '<table_name>'::regclass::oid
AND attsortkeyord = 1;

• その列でMERGE JOINを期待するか
  • 以下の条件を全て満たすとき、最も高速な結合操作であるMERGE JOINが行われる
    • 2つのテーブルで同じソートキーが指定され、同じ列で分散されている
    • どちらのテーブルも80%以上ソートされている
    • 2つのテーブルがJOIN条件でDISTKEY列とSORTKEY列の両方を使用して結合されている

分散スタイルの決定

• そのテーブルは結合に使用されているか
  • 結合に使用されていなければ、ALL分散の重複コストに対するメリットがないため、ALL分散が選択肢になくなる
  • 結合はJOINだけでなく、IN、NOT IN、MINUS、EXCEPT、INTERSECT、EXISTSなどでも使われるので注意

SELECT COUNT(*) FROM (
SELECT DISTINCT query
FROM stl_scan
WHERE tbl = '<table_name>'::regclass::oid
AND type = 2
AND userid > 1
INTERSECT (
SELECT DISTINCT query FROM stl_hashjoin
UNION
SELECT DISTINCT query FROM stl_nestloop
UNION
SELECT DISTINCT query FROM stl_mergejoin
));

• 少なくともの1つの分散キー候補があるか
• 追加ストレージを許容できるか
  • ALL分散では、そのテーブルが全てのノードにコピーされる

SELECT "table", size, pct_used,
CASE diststyle
WHEN 'ALL' THEN size::TEXT
ELSE '< ' || size * (
SELECT COUNT(DISTINCT node) FROM stv_slices)
END est_distall_size,
CASE diststyle
WHEN 'ALL' THEN pct_used::TEXT
ELSE '< ' || pct_used * (
SELECT COUNT(DISTINCT node) FROM stv_slices)
END est_distall_pct_used
FROM svv_table_info
WHERE table_id = '<table_name>'::regclass::oid;

• 並列性能を犠牲にすることを許容できるか
  • ALL分散は並列性能を犠牲にする
  • ALL分散では1行の更新を全てのノードで更新
  • 結合に使用する場合
    • ネットワークIOが減る
    • 計算量とディスクIOは増える
  • ALL分散が不向きな一般的なガイドライン
    • 読み取り
      • 大きなファクトテーブルへのスキャン
      • 結合しない単一テーブルスキャン
      • ウインドウ集約関数など、複雑な集計のあるテーブルへのスキャン
    • 書き込み
      • DMLで頻繁に変更されるテーブル
      • 膨大なデータをロードするテーブル
      • VACUUM操作で頻繁にメンテナンスする必要があるテーブル

SELECT '<table_name>'::regclass::oid AS table_id,
(SELECT count(*) FROM (
SELECT DISTINCT query FROM stl_insert
WHERE tbl = '<table_name>'::regclass::oid
INTERSECT
SELECT DISTINCT query FROM stl_delete
WHERE tbl = '<table_name>'::regclass::oid
)) AS num_updates,
(SELECT count(*) FROM (
SELECT DISTINCT query FROM stl_delete
WHERE tbl = '<table_name>'::regclass::oid
MINUS
SELECT DISTINCT query FROM stl_insert
WHERE tbl = '<table_name>'::regclass::oid
)) AS num_deletes,
(SELECT COUNT(*) FROM (
SELECT DISTINCT query FROM stl_insert
WHERE tbl = '<table_name>'::regclass::oid
MINUS
SELECT distinct query FROM stl_s3client
MINUS
SELECT DISTINCT query FROM stl_delete
WHERE tbl = '<table_name>'::regclass::oid
)) AS num_inserts,
(SELECT COUNT(*) FROM (
SELECT DISTINCT query FROM stl_insert
WHERE tbl = '<table_name>'::regclass::oid
INTERSECT
SELECT distinct query FROM stl_s3client
)) as num_copies,
(SELECT COUNT(*) FROM (
SELECT DISTINCT xid FROM stl_vacuum
WHERE table_id =
'<table_name>'::regclass::oid
AND status NOT LIKE 'Skipped%'
)) AS num_vacuum;

• 少なくとも1つの分散キー候補があるか
• 結合条件で分散キー候補を使用するか

最適な分散キーの決定

• どのクエリーを優先すべきか
  • クエリーごとに結合条件に使用する列が異なる場合、どのクエリーを優先すべきか、から分散キーを選ぶ
  • どのクエリーを優先するかは業務用件次第

ソートキーについて

• ソートするメリット
  • ゾーンマップによりディスクIOを削減
  • クエリー実行時のソートをなくす
  • MERGE JOINによって結合のパフォーマンスを向上
• ソート形式の種類
  • COMPOUND：
    • 定義した順序が重要
    • 第1ソートキーが使用されない場合は、第2ソートキー以降も使われない
  • INTERLEAVED：
    • 定義した全ての列が同じ重要度
    • 第1ソートキーを使う場合はCOMPOUNDより少し遅いが、使わない場合は圧倒的に速い
    • メンテナンスコストが非常に高い
  • 一般的に、90%のケースでCOMPOUNDで十分

ソート形式の決定

• ソートはMERGE JOINを有効にするか
  • 以下の条件を全て満たすとき、最も高速な結合操作であるMERGE JOINが行われる
    • 2つのテーブルで同じソートキーが指定され、同じ列で分散されている
    • どちらのテーブルも80%以上ソートされている
    • 2つのテーブルがJOIN条件でDISTKEY列とSORTKEY列の両方を使用して結合されている
• ソートは実行時のソート処理を削減するか
  • ORDER BY、GROUP BYおよび、WINDOW関数内のPARTITION BY、ORDER BYはソート処理が行われる
  • 指定の列を事前にソートしておけばクエリー時のソート処理を減らせる
• ソートはゾーンマップを改善できるか
  • 改善できないケース
    • 各スライスに1MBブロックが1つしかない
    • 列に1つの値のみが含まれている
• クエリーは様々な列でフィルターするか
  • ソートキーはテーブルに1つ
  • クエリーでフィルタの列が異なる場合、どのクエリーを優先すべきかからソートキーを選ぶべき
• 必要に応じてVACUUM REINDEXできるか
  • COMPOUND SORTKEYはソート済みデータをロードする場合はVACUUM不要
  • INTERLEAVED SORTKEYはロード後にVACUUM REINDEXしないと効果が弱まる
  • VACUUM REINDEXはIOコストが非常に高い
• データに9バイト以上のPrefixがあるか
  • COMPOUND SORTKEYは先頭8バイトまでしかソート順に考慮しない（https://で始まるURLなどではCOMPOUND SORTKEYは使用できないことに注意）
  • INTERLEAVED SORTKEYはデータ全体をソート順に考慮

最良のソートキー列の決定

• MERGE JOINのためのソートキー：DISTKEYと同じ列
• ソート処理を削減するためのソートキー：ORDER BYなどで使われる列
• ゾーンマップを改善するためのソートキー：フィルターされる列

まとめ

• 分散スタイルの決定方法：結合に注目
  1. KEY分散に向く列が存在するか検討
  2. ALL分散に向くか検討
• ソートキーの決定方法：結合、ORDER BY、フィルターに注目
  • なんのためにソートするのか検討
  • ゾーンマップの改善のための場合にはINTERLEAVEに向くか検討

今後のオンラインセミナー

• AWS オンラインセミナースケジュール

9月以降もいろいろなサービス/ソリューションの紹介があります。

最後に

Redshiftのテーブル設計の指針として、大変参考になりました。

ベストプラクティスは理解していても、実際にうまく設計していくのは難しいケースが多いですが、フローチャートで判定していけるのでとても助かりますね。